﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data;

using NetworkAssetManager.General;
using NetworkAssetManager.DataAccess;
using NetworkAssetManager.Entity;


namespace NetworkAssetManager.DataAccess
{
    public class ScanDb : BaseDB
    {
        public int CreateTable()
        {
            throw new System.NotImplementedException();
        }

        public int InsertScan(EntScan scan)
        {
            int retScanID = -1; 
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                      command.CommandText = @"
                            INSERT INTO Scan    
                                (MachineID, ScanDate)
                                 VALUES (?, ?);";
                        command.Parameters.Add(new SqlCeParameter("MachineID",
                            SqlDbType.Int, 4)).Value = scan.MachineID;
                        command.Parameters.Add(new SqlCeParameter("ScanDate",
                            SqlDbType.DateTime)).Value = DateTime.Now;

                        command.ExecuteNonQuery();

                        command.CommandText = @"select @@identity ";
                        retScanID = Convert.ToInt32(command.ExecuteScalar());
                    }
                    transaction.Commit();
                }
            }
            catch (Exception)
            {
                retScanID = -1; 
            }
            return retScanID;
        }

        public List<EntScan> GetScans()
        {
            List<EntScan> list = new List<EntScan>();

            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"
                          SELECT * FROM [Scan] ";

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(DataReaderToObject(reader));
                        }
                    }


                }
            }
            catch (Exception) { }
            return list;
        }

        public List<EntScan> GetScanDetails(int machineID)
        {
            List<EntScan> list = new List<EntScan>();
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"
                          SELECT * FROM [Scan] where MachineID = ?";

                    command.Parameters.Add(new SqlCeParameter("MachineID",
                        SqlDbType.Int, 4)).Value = machineID;

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(DataReaderToObject(reader));
                        }
                    }
                }
            }
            catch (Exception) { }
            return list;
        }

        public EntScan GetLatestScanDetails(int machineID)
        {
            EntScan objScan = null; 
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"
                          SELECT TOP (1) * FROM [scan] where MachineID = ? ORDER BY [ScanDate] desc;";

                    command.Parameters.Add(new SqlCeParameter("MachineID",
                        SqlDbType.Int, 4)).Value = machineID;

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            objScan = DataReaderToObject(reader);
                        }
                    }


                }
            }
            catch (Exception) { }
            return objScan;
        }

        public EntScan GetLatestScanDetails(int machineID, DateTime scanDate)
        {
            EntScan objScan = null;
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"SELECT * FROM [scan] where MachineID = ? and ScanDate = ?;";
                    command.Parameters.Add(new SqlCeParameter("MachineID",
                        SqlDbType.Int, 4)).Value = machineID;


                    command.Parameters.Add(new SqlCeParameter("ScanDate",
                        SqlDbType.DateTime)).Value = scanDate;


                    //DateTime.Today.ToString("dd/mm/yyyy hh:mm:ss:mmm");

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            objScan = DataReaderToObject(reader);
                        }
                    }


                }
            }
            catch (Exception) { }
            return objScan;
        }


        public void DeleteScans (int machineID)
        {
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                              DELETE FROM [Scan] where MachineID = ?";

                        command.Parameters.Add(new SqlCeParameter("MachineID",
                            SqlDbType.Int, 4)).Value = machineID;

                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                        }
                    }
                }
            }
            catch (Exception) { }
        }


        private EntScan DataReaderToObject(SqlCeDataReader reader)
        {
            EntScan scan = new EntScan();

            scan.MachineID = reader.GetInt32(reader.GetOrdinal("MachineID"));
            scan.ScanID = reader.GetInt32(reader.GetOrdinal("ScanID"));
            scan.Date = reader.GetDateTime(reader.GetOrdinal("ScanDate")); 

            return scan;
        }
    }
}
